Project Topic: Coronavirus Disease (COVID-19) Data Analysis¶
Project Description¶
Overview¶
Within months, COVID-19 went from an epidemic to a pandemic and has affected people's lives in many ways all over the world. From the first identified case in December 2019, how did the virus spread so fast and widely?
Out of curiosity, I explored the open dataset from the World Health Organization website and tried to applied the analysis skills all together I have learned to this ongoing real-world problem.
Data Availability¶
The main dataset was acquired from the World Health Organization website, date ranging from January 11, 2020, as to June 20, 2020.
- Data Source: https://covid19.who.int/
Project Workflow¶
In the first part, I mainly used Python programming with Numpy and Pandas packages for data wrangling including clean and merge datasets. In terms of exploratory data analysis, several visualization tools used include Matplotlib and seaborn. I also self-learned "plotly express", a high-level interface to create many fancy interactive and informative visualizations.
Secondly, I Integrated Jupyter notebook and MySQL Workbench through API to migrate data from Pandas DataFrame to SQL table
Last but not least, I connected the MySQL server to Tableau. I built a dashboard and deployed it to the public website for offering interactive data visualization.
- Visual Dashboard site: https://christinachiang753951.github.io/Tableau/COVID-19/Dashboard.html
In [1]:
# Import the relevant libraries
import pandas as pd
import numpy as np
import seaborn as sns
import matplotlib.pyplot as plt
# Suppress all warning
import warnings
warnings.simplefilter(action='ignore')
# Expand the output display to see more columns of DataFrame
pd.set_option('display.max_columns', 50)
# print all the outputs in a cell
from IPython.core.interactiveshell import InteractiveShell
InteractiveShell.ast_node_interactivity = "all"
In [2]:
# Load the data
df_WHO = pd.read_csv('WHO_COVID19_globaldata.csv')
In [3]:
# Eyeball the data
df_WHO.head()
Out[3]:
- As we can see above, the data is recorded in country-based.
Exploratory data analysis¶
In [4]:
# There are 23022 observations & 8 columns in this DataFrame
df_WHO.shape
Out[4]:
In [5]:
df_WHO.info()
In [6]:
# Convert all Date to datetime format
df_WHO['Date_reported']=pd.to_datetime(df_WHO['Date_reported'])
In [7]:
# Check again column type and we can see Date_reported is recorded under UTC timezone
df_WHO.info()
In [8]:
# Date ranges from January 11, 2020, as of June 20, 2020.
df_WHO.Date_reported.min(), df_WHO.Date_reported.max()
Out[8]:
In [9]:
df_WHO.describe(include ='all')
Out[9]:
In [10]:
# Taiwan is excluded from the World Health Assembly
df_WHO[df_WHO['Country'].str.startswith('Taiwan')]
Out[10]:
Missing data Exploration¶
In [11]:
df_WHO.isna().any()
Out[11]:
In [12]:
df_WHO.isna().sum()
Out[12]:
- There are two variables "Country_code" & "WHO_region" with missing values.
In [13]:
# Missing Country_code are all from Namibia
df_WHO[df_WHO.Country_code.isna() == True]['Country'].nunique()
df_WHO[df_WHO.Country_code.isna() == True].head(3)
Out[13]:
Out[13]:
In [14]:
# For missing values in WHO_region columns are all undefined Country, later will remove them
df_WHO[df_WHO.WHO_region.isna() == True].head(3)
Out[14]:
Six WHO regions¶
The World Health Organization (WHO) divides the world into six WHO regions, for the purposes of reporting, analysis and administration.
- African Region (AFRO)
- Region of the Americas (PAHO)
- South-East Asia Region (SEARO)
- European Region (EURO)
- Eastern Mediterranean Region (EMRO)
- Western Pacific Region (WPRO)
Handle Missing Data¶
- Fill missing values: 2 digit ISO of Namibia is "NA"
- Source : https://countrycode.org/namibia
In [15]:
df_WHO.Country_code.fillna('NA',inplace=True)
In [16]:
df_WHO.isna().sum()
Out[16]:
In [17]:
# Drop the rows with WHO_region = NaN
df_WHO.dropna(how='any',subset=['WHO_region'],inplace=True)
In [18]:
# Check: Now I finished the first-step data cleaning process
df_WHO.isna().sum()
Out[18]:
Manually add Taiwan COVID-19 data¶
In [19]:
# Load the data (This dataset includes Taiwan)
df = pd.read_csv('covid-data.csv')
In [20]:
df.head()
Out[20]:
In [21]:
# Extract Taiwan observations only
df_TW = df[df['location'] == 'Taiwan']
df_TW.head()
Out[21]:
In [23]:
# There are lots of unnecessary columns and later will remove them
df_TW.info()
In [24]:
# Only fetch the fields common with df_WHO DataFrame
df_TW = df_TW[['date','iso_code','location','new_cases',\
'total_cases','new_deaths','total_deaths']]
In [25]:
df_TW.head()
Out[25]:
In [26]:
# Taiwan belongs to WPRO region, I added WHO_region columns in df_TW DataFrame
df_TW.insert(3,'WHO_region','WPRO')
In [27]:
df_TW.head(5)
Out[27]:
In [28]:
# Make the date column of type datetime(UTC timezone)
df_TW['date']=pd.to_datetime(df_TW['date']).dt.tz_localize('UTC')
In [29]:
df_TW.info()
In [30]:
df_TW['date'].min(), df_TW['date'].max()
Out[30]:
In [31]:
df_TW.head()
Out[31]:
In [32]:
df_WHO.columns.tolist()
Out[32]:
In [33]:
# Re-assign df_TW column name to be consistent with that of df_WHO
df_TW.columns = df_WHO.columns.tolist()
df_TW.head()
Out[33]:
In [34]:
# Remove the date with 0 Cumulative_cases
df_TW = df_TW[df_TW['Cumulative_cases'] != 0]
In [35]:
# Reset index starting from 0
df_TW.index = range(len(df_TW))
In [36]:
df_TW.head()
Out[36]:
Missing Data Exploration¶
In [37]:
df_TW.isna().sum()
Out[37]:
In [38]:
df_TW.isna().sum()
Out[38]:
In [39]:
# Missing values are all from the same record
df_TW[df_TW['New_cases'].isna()]
Out[39]:
In [40]:
# Now I can extract specific records based on the internal index
df_TW.iloc[45:50,]
Out[40]:
In [41]:
# Should fill values same as the previous record to make it complete
df_TW.fillna(method='ffill',inplace = True)
In [42]:
# Check it again
df_TW.iloc[45:50,]
Out[42]:
In [43]:
# Now I finished the second step data cleaning procedure
df_TW.isna().sum()
Out[43]:
Combine df_WHO & df_TW and name it as "df_World"¶
In [44]:
df_World = pd.concat([df_WHO, df_TW], axis = 0)
In [45]:
df_World.head()
Out[45]:
In [46]:
df_World.tail()
Out[46]:
In [47]:
# Reset index starting from 0
df_World.index = range(len(df_World))
df_World.tail()
Out[47]:
In [48]:
df_World.shape
Out[48]:
In [49]:
df_World.info()
In [50]:
df_World.describe(include ='all')
Out[50]:
- Now we are ready to do more analysis with this clean data
Data Overview¶
- Earliest/first day of the case being recorded by WHO is 2020-01-11
In [51]:
df_World.Date_reported.min()
Out[51]:
- Earliest case happened in China
In [52]:
df_World[(df_World.Date_reported == df_World.Date_reported.min())]
Out[52]:
- Check how many total days this data covers
In [53]:
df_World.Date_reported.nunique()
Out[53]:
- Check how many different countries had confirmed cases within January
In [54]:
df_World[(df_World.Date_reported < '2020-02-01') & (df_World.Cumulative_cases != 0)]\
['Country'].nunique()
Out[54]:
- Explroe how many different country had confirmed cases?
In [55]:
df_World['Country'].nunique()
Out[55]:
- Explore the earliest date there are a total of 10 countries have deaths reported.
In [56]:
df_death = df_World[df_World.Cumulative_deaths != 0]
df_death.head()
Out[56]:
In [57]:
Confirmed_death = df_death.groupby(by = 'Date_reported')['Country'].nunique()
Confirmed_death
Out[57]:
In [58]:
Confirmed_death[Confirmed_death == 10].index[0]
Out[58]:
Regional-based analysis¶
- Consider all confirmed cases, explore how many accumulated cases & accumulated deaths in 6 WHO regions separately
In [59]:
df_region = df_World.groupby('WHO_region')[['New_cases','New_deaths']].sum().reset_index().\
rename(columns={'New_cases':'Cumulative_cases','New_deaths':'Cumulative_deaths'})
df_region.head()
Out[59]:
* Calculate Case Fatality Rate (CFR)¶
The Case Fatality Rate (CFR) is the ratio between confirmed deaths and confirmed cases.
In [60]:
df_region['CFR']= round((df_region['Cumulative_deaths']/df_region['Cumulative_cases']),3)
df_region.sort_values('CFR', ascending = False)
Out[60]:
Data Visualization - Plot the data¶
- Seaborn
In [61]:
# Bar chart
sns.catplot(x='WHO_region', y='Cumulative_cases',data=df_region, kind='bar',aspect=1.5)
sns.catplot(x='WHO_region', y='Cumulative_deaths',data=df_region, kind='bar',aspect=1.5)
Out[61]:
Out[61]:
- Matplotlib
In [68]:
# pie chart
fig = plt.figure(2, figsize=(6,6))
labels=['AFRO', 'AMRO', 'EMRO', 'EURO','SEARO','WPRO']
explode=(0.1,0,0.1,0,0.1,0.1)
_ = plt.pie(df_region['Cumulative_cases'],explode=explode,labeldistance=1.35,pctdistance=1.2, \
labels=labels,autopct='%1.1f%%',
shadow=True,startangle=-40)
_ = plt.title('Cumulative_cases across 6 WHO Region')
plt.show()
fig = plt.figure(2, figsize=(6,6))
labels=['AFRO', 'AMRO', 'EMRO', 'EURO','SEARO','WPRO']
explode=(0.1,0,0.1,0,0.1,0.1)
_ = plt.pie(df_region['Cumulative_deaths'],explode=explode,labeldistance=1.35,pctdistance=1.2, \
labels=labels,autopct='%1.1f%%',
shadow=True,startangle=-40)
_ = plt.title('Cumulative_deaths across 6 WHO Region')
plt.show()
- Plotly Express: a high-level interface of plotly.py that allows us to create many interactive and informative visualizations.
In [65]:
import plotly.express as px
In [66]:
# bar chart
fig = px.bar(df_region, x="WHO_region", y="Cumulative_deaths", \
color="WHO_region")
fig.show()
In [67]:
# pie chart
fig = px.pie(df_region, values='Cumulative_deaths', names='WHO_region', \
title='Cumulative_deaths across 6 WHO Region')
fig.show()
Country-based analysis¶
In [68]:
df_country = df_World.groupby(by = ['WHO_region','Country','Country_code'])\
[['New_cases','New_deaths']].sum()\
.rename(columns={'New_cases':'Cumulative_cases','New_deaths':'Cumulative_deaths'})
In [69]:
df_country.head()
Out[69]:
In [70]:
# Alternatively
df_country = df_World.groupby(by = 'Country')[['Country_code','Cumulative_cases',\
'Cumulative_deaths','WHO_region']].max()
In [71]:
df_country.head()
Out[71]:
- Explore what's the top 5 country has the most confirmed cases as of June 20, 2020
In [72]:
df_country.sort_values(by=['Cumulative_cases'], ascending = False).head()
Out[72]:
In [73]:
# Alternatively
df_country['Cumulative_cases'].nlargest(5)
Out[73]:
- Explore what's the top 5 Country has the fewest confirmed cases as of June 20, 2020
In [74]:
df_country['Cumulative_cases'].nsmallest(5)
Out[74]:
- Explore what's the top 5 Country has the highest confirmed deaths as of June 20, 2020
In [75]:
df_country['Cumulative_deaths'].nlargest(5)
Out[75]:
In [76]:
# Calculate Case Fatality Rate (CFR)
df_country['CFR']= round((df_country['Cumulative_deaths']/df_country['Cumulative_cases']),3)
df_country.head()
Out[76]:
- Explore what's the top 5 Country has the highest CFR as of June 20, 2020
In [77]:
df_country.sort_values(by = 'CFR', ascending = False).head(5)
Out[77]:
In [78]:
# In order to plot the data, reset the index
df_country = df_country.reset_index()
df_country.head()
Out[78]:
Data Visualization - Plot the data¶
- Plotly Express
In [79]:
# scatter plot:
# It seems that there is a positive relationship between 'Cumulative_deaths' &'Cumulative_cases'
fig = px.scatter(df_country, x="Cumulative_deaths", y='Cumulative_cases',\
trendline="ols",hover_name="Country")
fig.show()
In [80]:
# scatter plot: larger circle means higher CFR for that country
# United States of America on the right top is an outlier
fig = px.scatter(df_country, x="Cumulative_deaths", y='Cumulative_cases',\
size="CFR", color="WHO_region", hover_name="Country", size_max=30, \
template = 'plotly_dark')
fig.show()
In [81]:
# treemaps：larger size of rectangles means more Cumulative_cases
# Color the closer to blue means higher CFR(Case Fatality Rate.
# As we can see France has high CFR.
fig = px.treemap(df_country, path=['WHO_region', 'Country'], values='Cumulative_cases', \
color='CFR',hover_data=['Country'], color_continuous_scale='RdBu')
fig.show()
In [82]:
# maps
# Something weird: It only shows Taiwan but not any other countries
# Assume it is a problem of Country_code (Taiwan is the only one whose Country_code is 3-digit)
fig = px.scatter_geo(df_country, locations="Country_code", color='CFR', hover_name="Country",\
projection="natural earth")
fig.show()
- In order to be consistent with other countries, I changed the iso_code from TWN (Alpha-3 code) to TW (Alpha-2 code), which will be used later.
In [83]:
df_country.replace('TWN', 'TW', inplace = True)
In [84]:
df_country[df_country.Country == 'Taiwan']
Out[84]:
I managed to change Country_code from current 2-digit to 3-digit
In [85]:
# Load the data
iso_alpha = pd.read_csv('wikipedia_isocode.csv')
iso_alpha.head()
Out[85]:
In [86]:
# drop unnecessary columns
iso_alpha.drop(columns = ['English short name lower case',\
'Numeric code','ISO 3166-2'], inplace = True)
In [87]:
iso_alpha.shape
Out[87]:
In [88]:
# Merge df_country & iso_alpha
df = df_country.merge(iso_alpha, left_on='Country_code',right_on='Alpha-2 code', how='left')
In [89]:
df.head()
Out[89]:
In [90]:
# Find out those not been matched: there are 6 records in total
df['Alpha-3 code'].isna().sum()
Out[90]:
In [91]:
iso_missing = df[df['Country_code'] != df['Alpha-2 code']]
iso_missing
Out[91]:
- After finding out exact countries whose Alpha-3 code is missing, I added them manually
source: https://countrycode.org/
In [92]:
iso_missing['Alpha-3 code'] = ['SSD','XKX','SXM','CUW','BES','NAM']
In [93]:
iso_missing.head()
Out[93]:
In [94]:
# Extract those countries Alpha-3 code are matched
df_noNA = df[df['Country_code'] == df['Alpha-2 code']]
In [95]:
# Concatenate df_noNA & iso_missing
df = pd.concat([df_noNA, iso_missing], axis = 0)
In [96]:
df.tail()
Out[96]:
In [97]:
df['Alpha-3 code'].isna().sum()
Out[97]:
In [98]:
# maps: Try it again and get the correct one this time
# Color the closer to yellow means higher CFR(Case Fatality Rate).
# As we can see Yemen stands out.
fig = px.choropleth(df, locations="Alpha-3 code", color='CFR', hover_name="Country", \
projection="natural earth")
fig.show()
Time Series Analysis¶
By exploring the number of new cases & new deaths to see whether the epidemic has turned to be stable or slowed down
In [99]:
df_World.head()
Out[99]:
- Explore new confirmed cases over time
In [100]:
df_date_case = df_World.groupby('Date_reported')['New_cases'].sum().reset_index()
In [101]:
df_date_case.head()
Out[101]:
In [102]:
df_date_case['binned_Dates'] = pd.cut(df_date_case.Date_reported, 5)
df_date_case.head()
Out[102]:
Data Visualization - Plot the data¶
- Seaborn
In [103]:
sns.catplot(y='binned_Dates', x='New_cases', data=df_date_case, kind='bar',aspect=3)
Out[103]:
- Explore new deaths over time
In [104]:
df_date_death = df_World.groupby('Date_reported')['New_deaths'].sum().reset_index()
df_date_death.head()
Out[104]:
In [105]:
df_date_death['binned_Dates'] = pd.cut(df_date_death.Date_reported, 5)
df_date_death.head()
Out[105]:
In [106]:
sns.catplot(y='binned_Dates', x='New_deaths', data=df_date_death, kind='bar',aspect=3)
Out[106]:
- For each observationDate, consider all countries, draw a graph shows 2 curves, 1st one for Confirmed total cases, 2nd one for Deaths
In [107]:
df_date = df_World.groupby('Date_reported').sum()[['New_cases','New_deaths']]
df_date.head()
Out[107]:
- Matplotlib
In [108]:
df_date.plot(title = 'New cases & New deaths over time for all over the world')
Out[108]:
- Plotly Express
In [109]:
# First I need to make data type of Date_reported string
# Otherwise, it will hit an error later while using Plotly Express
df_date = df_date.reset_index()
df_date['Date_reported'] = df_date['Date_reported'].astype('str').apply(lambda x : x[:10])
df_date.head()
Out[109]:
In [110]:
fig = px.line(df_date, x='Date_reported', y='New_cases', \
labels={'y':'New_cases'},
title='New cases over time for all over the world' )
fig.show()
fig = px.line(df_date, x='Date_reported', y='New_deaths', \
labels={'y':'New_deaths'},
title='New deaths over time for all over the world')
fig.show()
Interaction: You can explore any country you would like to know by inputting the country name¶
In [111]:
country = input("Please input the country name:")
df_country = df_World[df_World.Country == country].set_index('Date_reported')\
[['New_cases','New_deaths']]
df_country.plot( title = 'New cases & New deaths over time for ' + '%s' %country)
Out[111]:
In [112]:
country = input("Please input the country name:")
fig = px.line(df_World[df_World.Country == country], x='Date_reported', y='New_cases', \
labels={'y':'New_cases'},
title='New cases over time for ' + '%s' %country)
fig.show()
fig = px.line(df_World[df_World.Country == country], x='Date_reported', y='New_deaths', \
labels={'y':'New_deaths'},
title='New deaths over time for ' + '%s' %country)
fig.show()
- Consider all confirmed cases, draw a graph show how many accumulated cases in Mainland China and the other curve for all non-China in January
In [113]:
df_China = df_World[df_World['Country'] == 'China'].set_index('Date_reported')['Cumulative_cases']
In [114]:
df_China_Jan = df_China[df_China.index < '2020-02-01']
df_China_Jan.head()
Out[114]:
In [115]:
df_nonChina = df_World[df_World['Country'] != 'China']
df_nonChina.head()
Out[115]:
In [116]:
df_nonChina = df_nonChina.groupby('Date_reported')['Cumulative_cases'].sum()
df_nonChina.head()
Out[116]:
In [117]:
df_nonChina_Jan = df_nonChina[df_nonChina.index < '2020-02-01']
In [118]:
plt.plot(df_China_Jan ,'r')
plt.plot(df_nonChina_Jan ,'b')
plt.legend(['Cumulative_cases_China','Cumulative_cases_NonChina']) # åŠ ä¸Šåœ–ä¾‹
plt.show()
In [119]:
df.head() # Each row of this DataFrame is country-based, Date_reported column is not included
Out[119]:
In [120]:
iso = df[['Country_code','Alpha-3 code']]
iso.head()
Out[120]:
In [121]:
iso.isna().sum()
Out[121]:
In [122]:
iso.shape
Out[122]:
In [123]:
# change Country_code of df_World from TWN to TW, otherwise it will not be matched
df_World.replace('TWN', 'TW', inplace = True)
df_World.head()
Out[123]:
In [124]:
# Merge df_World & iso dataframe
df_plot = df_World.merge(iso, on = 'Country_code', how='left')
df_plot.head()
Out[124]:
In [125]:
df_plot.isna().sum()
Out[125]:
In [126]:
# In plotly express, the 'Date_reported' should be a string, otherwise it will hit an error
df_plot['Date_reported'] = df_plot.Date_reported.astype('str').apply(lambda x : x[:10])
In [127]:
# Reorder the dataframe based on Date_reported
df_plot = df_plot.sort_values('Date_reported')
df_plot.head()
Out[127]:
Explore the diffusion path of confirmed cases around the world.
Built-in Continuous Color Scales in Python source: https://plotly.com/python/builtin-colorscales/
In [128]:
# Color the closer to dark blue, the more serious situation is compared to other countries.
# China gets a lighter color over time indicates that it is getting less serious relative to otehrs
fig = px.choropleth(df_plot, locations="Alpha-3 code", color='Cumulative_cases'\
,hover_name="Country",animation_frame="Date_reported"\
,color_continuous_scale='Teal',template = 'plotly_white')
fig.show()
In [129]:
fig = px.choropleth(df_plot, locations="Alpha-3 code", color='Cumulative_deaths'\
,hover_name="Country",animation_frame="Date_reported"\
,color_continuous_scale='Reds',template = 'plotly_white')
fig.show()
Integrate Python & MySQL¶
migrate the information stored in the 'df_new_obs' data frame from Python to SQL
In [132]:
df_World.head()
Out[132]:
Importing the package will establish the connection between python and SQL which will so to speak build a bridge between the two pieces of software. I managed to establish a connection between workbench and Jupiter
Note: All cells below should be executed only once
In [165]:
import pymysql
To process any data from Python to SQL, I need to specifically designate the database I would like to connect to. Before that I created a schema/ database called "dbWHO" in MySQL Workbench
And then I create a Table called "COVID19" in MySQL dbWHO database
In [166]:
password = 'kitty33697'
In [167]:
conn = pymysql.connect(database = 'dbWHO', user = 'root', password = password)
cursor = conn.cursor()
Executing SQL queries from Python
Python variables can be used as arguments of the .execute() method as well.The relevant code has to be in SQL and be surrounded by parentheses.
In [168]:
# After I run the cell I obtain 0 which means there's currently no data stored in this table.
cursor.execute('SELECT * FROM COVID19;')
Out[168]:
In [169]:
# Make sure any data stored in the current DataFrame to be consistent with SQL syntax
df_World.Date_reported = df_World.Date_reported.astype('str').apply(lambda x : x[:10])
df_World.Date_reported = df_World.Date_reported.str.replace('-','/')
df_World.head(3)
Out[169]:
Creating a query and we will store as a string variable in Python and then use as an argument of the execute method. And it is the execute method that will move our data to the designated table in workbench.
The number of rows or values to be inserted into MySQL table with a single query may be limited. A limit has been set to twenty thousand rows and therefore I won't be able to complete the insert all at once.
I split the query into two parts neither of which can contain more than 20000 records.
In [170]:
# Creating the INSERT Statement
insert_query = 'INSERT INTO COVID19 VALUES '
for i in range(10000): # The first 10000 rows
insert_query += '('
# I need a second inner loop here which will repeat the next operation for every J.
for j in range(df_World.shape[1]): # 13 columns
# Turn the value obtained from each column into a string
# finally added to our insert statement
insert_query += "'" + str(df_World.iloc[i][j]) + "', "
insert_query = insert_query[:-2] + '), '
insert_query = insert_query[:-2] + ';'
In [171]:
cursor.execute(insert_query)
Out[171]:
- When executing an SQL statement affecting the database state from Python, must always commit to this action
In [172]:
conn.commit()
In [173]:
# This cell should be executed before running to the next cell, otherwise it will hit an error
df_World['Country'].replace(to_replace="Lao People's Democratic Republic",\
value='Laos',inplace=True)
In [174]:
insert_query = 'INSERT INTO COVID19 VALUES '
for i in range(10000,df_World.shape[0]): # insert remaining rows
insert_query += '('
# I need a second inner loop here which will repeat the next operation for every J.
for j in range(df_World.shape[1]): # 13 columns
# Turn the value obtained from each column into a string
# finally added to our insert statement
insert_query += "'" + str(df_World.iloc[i][j]) + "', "
insert_query = insert_query[:-2] + '), '
insert_query = insert_query[:-2] + ';'
In [175]:
cursor.execute(insert_query)
Out[175]:
In [176]:
conn.commit()
In [177]:
# Now I have added 23035 records to the WHO TABLE.
cursor.execute('SELECT * FROM COVID19;')
Out[177]:
In [178]:
# Export to csv
df_World.to_csv('COVID19.csv')